﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Transactions;
using System.Diagnostics;

namespace GenericLibrary.Database {

    public class Database {

        private DbProviderFactory _provider;
        private string _connectionString;

        public delegate void Batch();

        public bool Transact( Batch batch ) {
            using( TransactionScope ts = new TransactionScope() ) {
                try {
                    batch();
                    ts.Complete();
                    return true;
                } catch( Exception ) {
                    Debug.Assert( false );
                    return false;
                }
            }
        }

        public Database( string dbProviderName, string connectionString ) {
            try {
                this._provider = DbProviderFactories.GetFactory( dbProviderName );
                this._connectionString = connectionString;
            } catch( Exception ) {
                Debug.Assert( false );
                throw;
            }
        }

        public DataSet Load( string sql ) {
            try {
                DataSet dataSet = new DataSet();
                using( DbConnection connection = this._provider.CreateConnection() ) {
                    connection.ConnectionString = this._connectionString;
                    DbCommand selectCommand = this._provider.CreateCommand();
                    selectCommand.Connection = connection;
                    selectCommand.CommandText = sql;
                    DbDataAdapter adapter = this._provider.CreateDataAdapter();
                    adapter.SelectCommand = selectCommand;
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    adapter.Fill( dataSet );
                }
                return dataSet;
            } catch( Exception ) {
                Debug.Assert( false );
                throw;
            }
        }

        public object Execute( string sql ) {
            try {
                using( DbConnection connection = this._provider.CreateConnection() ) {
                    connection.ConnectionString = this._connectionString;
                    DbCommand selectCommand = this._provider.CreateCommand();
                    selectCommand.Connection = connection;
                    selectCommand.CommandText = sql;
                    connection.Open();
                    return selectCommand.ExecuteScalar();
                }
            } catch( Exception ) {
                Debug.Assert( false );
                throw;
            }
        }

        public int Update( DataSet dataSet ) {
            try {
                int rowsUpdated = 0;
                using( DbConnection connection = this._provider.CreateConnection() ) {
                    connection.ConnectionString = this._connectionString;
                    DbCommandBuilder commandBuilder = this._provider.CreateCommandBuilder();
                    commandBuilder.DataAdapter = this._provider.CreateDataAdapter();
                    commandBuilder.DataAdapter.AcceptChangesDuringUpdate = true;
                    rowsUpdated = commandBuilder.DataAdapter.Update( dataSet );
                }
                return rowsUpdated;
            } catch( Exception ) {
                Debug.Assert( false );
                throw;
            }
        }

        public DbDataReader Read( string sql ) {
            try {
                DbCommand command = this._provider.CreateCommand();
                command.Connection = this._provider.CreateConnection();
                command.Connection.ConnectionString = this._connectionString;
                command.CommandText = sql;
                command.Connection.Open();
                return command.ExecuteReader( CommandBehavior.KeyInfo | CommandBehavior.CloseConnection );
            } catch( Exception ) {
                Debug.Assert( false );
                throw;
            }
        }
    }
}
